import json
import pymysql
import requests

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3'
}

connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='cityData',
                             charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)


def traffic():
    url = 'https://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsnd&rowcode=zb&colcode=sj&wds=%5B%7B%22wdcode%22%3A%22reg%22%2C%22valuecode%22%3A%22440000%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22A0B07%22%7D%5D&k1=1711713964488&h=1'

    res = requests.get(url, headers=headers, verify=False, timeout=30)
    datas = list(json.loads(res.text)['returndata']['datanodes'])

    # 映射代码和对应的人口数据类型(辆)
    code_mapping = {
        'A0B0702': "公共汽电车运营数",
        'A0B0703': "轨道交通配属车辆数",
        'A0B0705': "公共汽电车运营线路总长度",  # (公里)
        'A0B0706': "轨道交通运营里程",
        'A0B0708': "公共汽电车客运量",  # (万人次)
        'A0B0709': "轨道交通客运量",
        'A0B070A': "出租汽车"  # (辆)
    }
    cursor = connection.cursor()
    unit = '辆'
    data = {category: [] for category in code_mapping.values()}
    for i in datas[1:]:
        if i['data']['data'] == 0 or i['wds'][0]['valuecode'] not in code_mapping.keys():
            continue
        if i['wds'][0]['valuecode'] == 'A0B0705' or i['wds'][0]['valuecode'] == 'A0B0706':
            unit = '公里'
        elif i['wds'][0]['valuecode'] == 'A0B0708' or i['wds'][0]['valuecode'] == 'A0B0709':
            unit = '万人次'

        d = {'year': i['wds'][2]['valuecode'], 'value': round(i['data']['data'], 2)}
        data[code_mapping[i['wds'][0]['valuecode']]].append(d)
        sql = ("INSERT INTO `environment` VALUES ('{}','{}','{}','{}','{}')"
               .format(i['wds'][2]['valuecode'], i['data']['data'], code_mapping[i['wds'][0]['valuecode']], '广东省',
                       unit))
        try:
            cursor.execute(sql)
        except:
            pass
    connection.commit()
    cursor.close()
    print(data)


def housingPrice():
    url = 'https://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsnd&rowcode=zb&colcode=sj&wds=%5B%7B%22wdcode%22%3A%22reg%22%2C%22valuecode%22%3A%22440000%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22A050J%22%7D%5D&k1=1711714460552&h=1'

    res = requests.get(url, headers=headers, verify=False, timeout=30)
    datas = list(json.loads(res.text)['returndata']['datanodes'])

    # 映射代码和对应的人口数据类型(元/平方米)
    code_mapping = {
        'A050J01': "商品房平均销售价格",
        'A050J02': "住宅商品房平均销售价格",
        'A050J03': "别墅、高档公寓平均销售价格",
        'A050J04': "办公楼商品房平均销售价格",
        'A050J05': "商业营业用房平均销售价格",
        'A050J06': "其他商品房平均销售价格"
    }
    cursor = connection.cursor()
    data = {category: [] for category in code_mapping.values()}
    for i in datas[1:]:
        if i['data']['data'] == 0:
            continue
        d = {'year': i['wds'][2]['valuecode'], 'value': round(i['data']['data'], 2)}
        data[code_mapping[i['wds'][0]['valuecode']]].append(d)
        sql = ("INSERT INTO `realestate` VALUES ('{}','{}','{}','{}','{}')"
               .format(i['wds'][2]['valuecode'], i['data']['data'], code_mapping[i['wds'][0]['valuecode']], '广东省',
                       '元/平方米'))
        try:
            cursor.execute(sql)
        except:
            pass
    connection.commit()
    cursor.close()
    print(data)


def houseSalesVolume():
    url = 'https://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsnd&rowcode=zb&colcode=sj&wds=%5B%7B%22wdcode%22%3A%22reg%22%2C%22valuecode%22%3A%22440000%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22A050I%22%7D%5D&k1=1711714648428&h=1'

    res = requests.get(url, headers=headers, verify=False, timeout=30)
    datas = list(json.loads(res.text)['returndata']['datanodes'])

    # 映射代码和对应的人口数据类型(亿元)
    code_mapping = {
        'A050I01': "商品房销售额",
        'A050I02': "住宅商品房销售额",
        'A050I03': "别墅、高档公寓销售额",
        'A050I04': "办公楼销售额",
        'A050I05': "商业营业用房销售额",
        'A050I06': "其他商品房销售额"
    }
    cursor = connection.cursor()
    data = {category: [] for category in code_mapping.values()}
    for i in datas[1:]:
        if i['data']['data'] == 0:
            continue
        d = {'year': i['wds'][2]['valuecode'], 'value': round(i['data']['data'], 2)}
        data[code_mapping[i['wds'][0]['valuecode']]].append(d)
        sql = ("INSERT INTO `realestate` VALUES ('{}','{}','{}','{}','{}')"
               .format(i['wds'][2]['valuecode'], i['data']['data'], code_mapping[i['wds'][0]['valuecode']], '广东省',
                       '亿元'))
        try:
            cursor.execute(sql)
        except:
            pass
    connection.commit()
    cursor.close()
    print(data)


def airQuality():
    url = 'https://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsnd&rowcode=zb&colcode=sj&wds=%5B%7B%22wdcode%22%3A%22reg%22%2C%22valuecode%22%3A%22440000%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22A0C06%22%7D%5D&k1=1711714890116&h=1'

    res = requests.get(url, headers=headers, verify=False, timeout=30)
    datas = list(json.loads(res.text)['returndata']['datanodes'])

    # 映射代码和对应的人口数据类型(万吨)
    code_mapping = {
        'A0C0601': "二氧化硫排放量",
        'A0C0602': "氮氧化物排放量",
        'A0C0604': "颗粒物排放量"
    }
    cursor = connection.cursor()
    data = {category: [] for category in code_mapping.values()}
    for i in datas[1:]:
        if i['data']['data'] == 0 or i['wds'][0]['valuecode'] not in code_mapping.keys():
            continue
        d = {'year': i['wds'][2]['valuecode'], 'value': round(i['data']['data'], 2)}
        data[code_mapping[i['wds'][0]['valuecode']]].append(d)
        sql = ("INSERT INTO `environment` VALUES ('{}','{}','{}','{}','{}')"
               .format(i['wds'][2]['valuecode'], i['data']['data'], code_mapping[i['wds'][0]['valuecode']], '广东省',
                       '万吨'))
        try:
            cursor.execute(sql)
        except:
            pass
    connection.commit()
    cursor.close()
    print(data)


def waterSupplyAndUsage():
    url = 'https://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsnd&rowcode=zb&colcode=sj&wds=%5B%7B%22wdcode%22%3A%22reg%22%2C%22valuecode%22%3A%22440000%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22A0C04%22%7D%5D&k1=1711715057301&h=1'

    res = requests.get(url, headers=headers, verify=False, timeout=30)
    datas = list(json.loads(res.text)['returndata']['datanodes'])

    # 映射代码和对应的数据类型(亿立方米)
    code_mapping = {
        'A0C0401': "供水总量",
        'A0C0402': "地表水供水总量",
        'A0C0403': "地下水供水总量",
        'A0C0404': "其他供水总量",
        'A0C0405': "用水总量",
        'A0C0406': "农业用水总量",
        'A0C0407': "工业用水总量",
        'A0C0408': "生活用水总量",
        'A0C0409': "生态用水总量",
        'A0C040A': "人均用水量"  # (立方米/人)
    }
    cursor = connection.cursor()
    unti = '亿立方米'
    data = {category: [] for category in code_mapping.values()}
    for i in datas[1:]:
        if i['data']['data'] == 0 or i['wds'][0]['valuecode'] not in code_mapping.keys():
            continue
        if i['wds'][0]['valuecode'] == 'A0C040A':
            unti = '立方米/人'

        d = {'year': i['wds'][2]['valuecode'], 'value': round(i['data']['data'], 2)}
        data[code_mapping[i['wds'][0]['valuecode']]].append(d)
        sql = ("INSERT INTO `environment` VALUES ('{}','{}','{}','{}','{}')"
               .format(i['wds'][2]['valuecode'], i['data']['data'], code_mapping[i['wds'][0]['valuecode']], '广东省',
                       unti))
        try:
            cursor.execute(sql)
        except:
            pass
    connection.commit()
    cursor.close()
    print(data)


if __name__ == '__main__':
    traffic()
    # housingPrice()
    # houseSalesVolume()
    # airQuality()
    # waterSupplyAndUsage()
